﻿using Hsp.Auto.Interface;

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Reflection;
using System.Text;
using Microsoft.Data.Sqlite;
using System.Security.Cryptography;
using System.Xml.Linq;
using Hsp.Auto.Model.Data;
using Hsp.Auto.SQLite.Models;
using System.Diagnostics;
using Hsp.Auto.Model.Models;
using log4net.DateFormatter;

namespace Hsp.Auto.SQLite
{
    public class TableService : SQLiteHelper, ITableService
    {
        //public TableService()
        //{
        //    //base.ConnectionString = MySqlHelperInit.ConnString;
        //}

        #region 表列表信息

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> TableList<T>()
        {
            return TableList<T>("", "");
        }

        /// <summary>
        /// 表列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> TableList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT rootpage AS Id, name AS Name, tbl_name AS Description
                FROM sqlite_master 
                WHERE (type='table') AND (name NOT IN ('__EFMigrationsHistory', 'sqlite_sequence')){0}
                ORDER BY tbl_name;", strWhere);

            return ListQuery<T>(strSql);
        }

        #endregion

        #region 表字段列表信息

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> ColumnList<T>()
        {
            return ColumnList<T>("", "");
        }

        /// <summary>
        /// 表字段列表信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ColumnList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;

            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT rootpage AS Id, name AS Name, tbl_name AS Description
                FROM sqlite_master 
                WHERE (type='table') AND (name NOT IN ('__EFMigrationsHistory', 'sqlite_sequence')){0}
                ORDER BY tbl_name;", strWhere);

            var dt = ExecuteQuery(strSql);

            foreach (DataRow dr in dt.Rows)
            {

            }



            //PRAGMA table_info("Medium");
            //cid, name, type,notnull, dflt_value, pk



            /*
            var strSql = string.Format(@"

            SELECT c.Ordinal_Position AS Id, c.Table_Name AS TableName, c.Column_Name AS NAME, c.Data_Type AS DataType
			, CAST(c.Column_Type AS CHAR(10000) CHARACTER SET utf8) AS ColumnType
			, CASE WHEN c.Is_Nullable = 'NO' THEN 0 ELSE 1 END AS Nullable
			, IFNULL(CASE WHEN c.Character_Maximum_Length > 2147483647 THEN 2147483646 ELSE c.Numeric_Scale END, 0) AS Character_Maximum_Length
			, IFNULL(c.Numeric_Precision, 0) AS Numeric_Precision
			, IFNULL(c.Numeric_Scale, 0) AS Numeric_Scale, CASE WHEN c.Column_Key = 'PRI' THEN 1 ELSE 0 END AS IsPK, c.Column_Key AS ColumnKey
			, CASE WHEN LENGTH(c.Extra) > 0 THEN 1 ELSE 0 END AS IsIdentity, c.Column_Comment AS Description, Column_Key
			, IFNULL(fk.REFERENCED_TABLE_NAME, '') AS Referenced_Table_Name, IFNULL(fk.REFERENCED_COLUMN_NAME, '') AS Referenced_Column_Name

			FROM information_schema.columns c
			LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE fk ON fk.table_schema = c.table_schema AND fk.Table_Name = c.Table_Name AND fk.Column_Name = c.Column_Name

			WHERE c.table_schema='{0}'{1} 
			ORDER BY c.Table_Name, c.Ordinal_Position;
			", dbName, strWhere);
                */

            return ListQuery<T>(strSql);
        }


        public List<Hsp.Auto.Model.Models.Columns> SqliteColumnList<T>(string tableNames)
        {
            var strWhere = string.Empty;

            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (tbl_name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT rootpage AS Id, name AS Name, tbl_name AS Description
                FROM sqlite_master 
                WHERE (type='table') AND (tbl_name NOT IN ('__EFMigrationsHistory', 'sqlite_sequence')){0}
                ORDER BY tbl_name;", strWhere);

            var dt = ExecuteQuery(strSql);


            List<Columns> columns = new();

            foreach (DataRow dr in dt.Rows)
            {
                var tableName = dr["Name"].ToString();
                strSql = string.Format($"PRAGMA table_info('{tableName}');");
                var columnDt = ExecuteQuery(strSql);

                List<SqliteColumns> sqliteColumns = columnDt?.ToList<SqliteColumns>() ?? null;
                //List<SqliteColumns> list1 = (List<SqliteColumns>)(dt?.ToEnumerable<SqliteColumns>() ?? null);

                //PRAGMA table_info("Medium");
                //cid, name, type,notnull, dflt_value, pk

                foreach (SqliteColumns sqliteColumn in sqliteColumns)
                {
                    var length = 0;
                    var dataType = sqliteColumn.type;
                    if (dataType != null)
                    {
                        dataType = dataType.Split('(')[0].TrimEnd(')');
                        int.TryParse(dataType, out length);
                    }

                    Hsp.Auto.Model.Models.Columns column = new();
                    column.Id = sqliteColumn.cid;
                    column.Name = sqliteColumn.name;
                    column.Comment = sqliteColumn.name;
                    column.TableName = tableName;
                    column.ColumnType = sqliteColumn.type;
                    column.DataType = sqliteColumn.type;
                    column.Nullable = Math.Abs(sqliteColumn.notnull - 1);
                    column.IsPK = sqliteColumn.pk;
                    column.IsIdentity = column.IsPK == 1 && sqliteColumn.type == "INTEGER" ? 1 : 0; ;
                    column.DefaultValue = sqliteColumn.dflt_value;
                    column.Length = length;

                    columns.Add(column);
                }
            }

            return columns;

            //throw new NotImplementedException();
        }

        #endregion

        #region 表被引用数据信息

        /// <summary>
        /// 表被引用数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> ReferenceList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            //if (!string.IsNullOrEmpty(tableNames))
            //{
            //    tableNames = tableNames.Trim().Replace(",", "','");
            //    strWhere = string.Format(@" AND (o.name IN ('{0}'))", tableNames);
            //}
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (c.table_name IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
			SELECT c.Ordinal_Position AS Id, c.Column_Name AS NAME, c.Table_Name AS TableName, IFNULL(pk.TABLE_NAME, '') AS Primarykey_Table_Name, IFNULL(pk.COLUMN_NAME, '') AS Primarykey_Column_Name
			FROM information_schema.columns c
			INNER JOIN information_schema.KEY_COLUMN_USAGE pk ON pk.REFERENCED_TABLE_SCHEMA = c.table_schema AND pk.REFERENCED_TABLE_NAME = c.Table_Name AND pk.REFERENCED_COLUMN_NAME = c.Column_Name
			WHERE c.table_schema='{0}'{1} 
			ORDER BY c.Table_Name, c.Ordinal_Position;
			", dbName, strWhere);

            return ListQuery<T>(strSql);

        }

        #endregion

        #region 表注释中可添加枚举数据信息

        /// <summary>
        /// 表注释中可添加枚举数据信息
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <returns></returns>
        public List<T> EnumList<T>(string dbName)
        {
            var strSql = string.Format(@"
            SELECT DISTINCT Column_Name AS NAME
            , Column_Comment AS Description
            FROM information_schema.columns
            WHERE table_schema='{0}' 
            AND LENGTH(Column_Comment) > 0
            AND (INSTR(Column_Comment, '：') > 0)
            AND (Data_Type = 'int')
            ORDER BY Column_Comment;
			", dbName);

            return ListQuery<T>(strSql);

        }

        #endregion

        public List<T> ColumnList<T>(string tableNames)
        {
            return ColumnList<T>("", tableNames);
        }

        public List<T> ReferenceList<T>(string tableNames)
        {
            return ReferenceList<T>("", tableNames);
        }

        public List<T> EnumList<T>()
        {
            return EnumList<T>("");
        }

        public List<T> ReferenceList<T>()
        {
            return ReferenceList<T>("");
        }

        /// <summary>
        /// 获取当前数据库名称
        /// </summary>
        /// <returns></returns>
        public string CurrentDbName()
        {
            return GetDbName();

            //throw new NotImplementedException();
        }

        public List<T> KeyColumnList<T>()
        {
            throw new NotImplementedException();
        }

        public List<T> KeyColumnList<T>(string tableNames)
        {
            throw new NotImplementedException();
        }

        public List<T> KeyColumnList<T>(string dbName, string tableNames)
        {
            throw new NotImplementedException();
        }


        public List<T> IndexList<T>()
        {
            return IndexList<T>("");
        }

        public List<T> IndexList<T>(string tableNames)
        {
            return IndexList<T>("", tableNames);
        }

        public List<T> IndexList<T>(string dbName, string tableNames)
        {
            var strWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableNames))
            {
                tableNames = tableNames.Trim().Replace(",", "','");
                strWhere = string.Format(@" AND (`tbl_name` IN ('{0}'))", tableNames);
            }

            var strSql = string.Format(@"
                SELECT tbl_name AS TableName, name AS IndexName, null AS `NonUnique`, null AS `IndexColumns`
                FROM sqlite_master 
                WHERE (type='index') AND (tbl_name NOT IN ('__EFMigrationsHistory', 'sqlite_sequence')){0}
                ORDER BY tbl_name;", strWhere);

            return ListQuery<T>(strSql);
        }

        public List<T> ViewList<T>(string dbName)
        {
            throw new NotImplementedException();
        }

        public List<T> ViewColumnList<T>(string dbName)
        {
            throw new NotImplementedException();
        }
    }
}
